#!usr/bin/env python
# *- coding:utf-8 -*-
# Andy Create @ 11/20/2019 4:48 PM
import pymysql

from DBUtils.PooledDB import PooledDB


class SqlHelper():
    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,
            maxconnections=5,
            mincached=1,
            blocking=True,
            ping=0,
            host='127.0.0.1',
            port=3306,
            user='root',
            password='zjgisadmin',
            database='flask_excel',
            charset='utf8')

    def open(self):
        conn = self.pool.connection()
        cursor = conn.cursor()
        return conn, cursor

    def close(self, cursor, conn):
        cursor.close()
        conn.close()

    def fetchall(self, sql, *args):
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchall()
        self.close(conn, cursor)
        return result

    def fetchone(self, sql, *args):
        conn, cursor = self.open()
        cursor.execute(sql, args)
        result = cursor.fetchone()
        self.close(conn, cursor)
        return result

    def commit_data(self, sql, *args):
        conn, cursor = self.open()
        cursor.execute(sql, args)
        conn.commit()
        self.close(conn, cursor)
